/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import models.Course;
import models.Lesson;
import models.LiveLesson;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author Achie
 */
public class CourseDao {

    private final Session session;

    public CourseDao(Session session) {
        this.session = session;
    }

    public List<Course> findAll() { // find all courses
        Transaction tx = session.beginTransaction();
        List<Course> list = session.createQuery("from Course as c ORDER BY c.name ASC").list();
        tx.commit();
        return list;
    }

    public List<Course> findAllByCategory(long id, int limit) { // find all courses filterd by Category
        Transaction tx = session.beginTransaction();
        List<Course> list = session.createQuery("from Course where category_id= :id ORDER BY name ASC").setParameter("id", id).setMaxResults(limit).list();
        tx.commit();
        return list;
    }

    public List<Course> findAllByTeacher(long id) { // find all courses filterd by Teacher
        Transaction tx = session.beginTransaction();
        String sql = "select c.course_id from Course_teacher c, employee e where c.teacher_id=e.id and e.id=:id";
        Query query = session.createSQLQuery(sql).setParameter("id", id);
        List idlist = query.list();
        ArrayList<Course> list = new ArrayList<Course>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Course where id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Course course = get(Long.parseLong(idlist.get(i).toString()));
            System.out.println(course.getName() + course.getCategory().getId() + course.getCurrency() + course.getDescription() + course.getId() + course.getPrice());
            list.add(course);
        }
        tx.commit();
        return list;
    }

    public List<Course> findAllByTeacherAndCategory(long id, long catid) { // find all courses filterd by Teacher and Category
        Transaction tx = session.beginTransaction();
        String sql = "SELECT c.id FROM course c LEFT JOIN course_teacher ct ON c.id = ct.course_id LEFT JOIN employee e ON ct.teacher_id = e.id WHERE c.category_id = :catid AND e.id = :id ORDER BY c.name ASC";
        Query query = session.createSQLQuery(sql).setParameter("id", id).setParameter("catid", catid);
        List idlist = query.list();
        ArrayList<Course> list = new ArrayList<Course>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Course where id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Course course = get(Long.parseLong(idlist.get(i).toString()));
            list.add(course);
        }
        tx.commit();
        return list;
    }

    public List<Course> findAllByStudent(long id) { // find all courses filterd by Student
        Transaction tx = session.beginTransaction();
        String sql = "SELECT course_id FROM course_student WHERE student_id= :id";
        Query query = session.createSQLQuery(sql).setParameter("id", id);
        List idlist = query.list();
        ArrayList<Course> list = new ArrayList<Course>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Course where id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Course course = get(Long.parseLong(idlist.get(i).toString()));
            list.add(course);
        }
        tx.commit();
        return list;
    }

    public List<Course> findAllByStudentAndCategory(long id, long catid) { // find all courses filterd by Student and Category
        Transaction tx = session.beginTransaction();
        String sql = "SELECT c.id FROM course c LEFT JOIN course_student cs ON c.id = cs.course_id LEFT JOIN student s ON cs.student_id = s.id WHERE c.category_id = :catid AND s.id = :id ORDER BY c.name ASC";
        Query query = session.createSQLQuery(sql).setParameter("id", id).setParameter("catid", catid);
        List idlist = query.list();
        ArrayList<Course> list = new ArrayList<Course>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Course where id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Course course = get(Long.parseLong(idlist.get(i).toString()));
            list.add(course);
        }
        tx.commit();
        return list;
    }    
    
    public List<Course> getPagination(long category_id, int page, int limit) { //get the pagination
        int offset = (page - 1) * limit;
        Transaction tx = session.beginTransaction();
        List<Course> list = session.createQuery("from Course WHERE category_id = :category_id ORDER BY name ASC").setParameter("category_id", category_id).setFirstResult(offset).setMaxResults(limit).list();
        tx.commit();
        return list;
    }
    
    public int getLastPage(long category_id) { // get the last page
        Transaction tx = session.beginTransaction();
        Query query = session.createSQLQuery("SELECT COUNT(*) FROM Course WHERE category_id = " + category_id);
        tx.commit();
        String i = "" + query.uniqueResult();
        return Integer.parseInt(i);
    }
    
    public List getFreeLessons() { // get the list of free lessons
        Transaction tx = session.beginTransaction();
        List list = session.createSQLQuery("SELECT DISTINCT c.*, l.* FROM course c LEFT JOIN lesson l ON l.course_id = c.id WHERE l.freelesson = 'FALSE' ORDER BY l.added_on DESC").addEntity(Course.class).addEntity(Lesson.class).setMaxResults(4).list();
        tx.commit();
        return list;
    }
    
    public List getNextLessons() { // get the list of next lessons
        Transaction tx = session.beginTransaction();
        List list = session.createSQLQuery("SELECT c.*, l.*, ll.* FROM course c LEFT JOIN lesson l ON l.course_id = c.id LEFT JOIN live_lesson ll ON ll.lesson_lesson_id = l.lesson_id WHERE l.livelesson = 'TRUE' ORDER BY ll.date ASC").addEntity(Course.class).addEntity(Lesson.class).addEntity(LiveLesson.class).setMaxResults(4).list();
        tx.commit();
        return list;
    }
    
    public List getUpdates(long member_id) { //get the list of newest updates
        Transaction tx = session.beginTransaction();
        List list = session.createSQLQuery("SELECT c.*, l.* FROM course c LEFT JOIN course_student cs ON c.id = cs.course_id LEFT JOIN lesson l ON l.course_id = c.id WHERE cs.student_id = :member_id ORDER BY l.added_on DESC").addEntity(Course.class).addEntity(Lesson.class).setParameter("member_id", member_id).setMaxResults(4).list();
        tx.commit();
        return list;
    }
    
    public List getNewestCourses() { // orders the list on newest courses
        Transaction tx = session.beginTransaction();
        List list = session.createSQLQuery("SELECT c.* FROM course c ORDER BY c.added_on DESC").addEntity(Course.class).setMaxResults(4).list();
        tx.commit();
        return list;
    }

    public boolean save(Course course) {
        try {
            Transaction tx = session.beginTransaction();
            session.save(course);
            tx.commit();
            return true;
        } catch (Exception e) {            
            return false;
        }
    }

    public boolean update(Course course) {
        try {
            Transaction tx = session.beginTransaction();
            session.update(course);
            tx.commit();
            return true;
        } catch (Exception e) {            
            return false;
        }
    }

    public Course get(long id) {
        return (Course) session.get(Course.class, id);
    }

    public void delete(Course course) {
        Transaction tx = session.beginTransaction();
        session.delete(course);
        tx.commit();
    }
}
